package com.project;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Set;
import java.util.regex.*;

public class DB_Flight extends DB_Operator {

    /**
     * Default constructor
     */
    public DB_Flight() {
    }

    /**
     * @param input 输入
     * @return 返回
     */
    public boolean add(List<String> input) {
        if (input.size() != 5) { // 因为id自增，所以输入只需要5个参数
            return false;
        }
        connect();
        String sql = "INSERT INTO `Flight` (`starttime`, `endtime`, `takeoffstation`, `arrivalstation`, `boardingtime`) VALUES (?,?,?,?,?)";
        try (PreparedStatement PStmt = con.prepareStatement(sql)) {
            PStmt.setString(1, input.get(0));
            PStmt.setString(2, input.get(1));
            PStmt.setString(3, input.get(2));
            PStmt.setString(4, input.get(3));
            PStmt.setString(5, input.get(4));
            int rowsAffected = PStmt.executeUpdate();
            close();
            return rowsAffected > 0;
        } catch (SQLException e) {
            System.err.println("出现异常：" + e.getMessage());
            close();
            return false;
        }
    }

    /**
     * @param input 输入
     * @return 返回
     */
    public boolean delete(List<String> input) {
        if (input.isEmpty()) {
            return false;
        }
        connect();
        String deleteSql = "DELETE FROM `Flight` WHERE `fid` =?";
        String maxIdSql = "SELECT MAX(fid) FROM `Flight`";
        String updateAutoIncrementSql = "ALTER TABLE `Flight` AUTO_INCREMENT =?";
        try (PreparedStatement deletePStmt = con.prepareStatement(deleteSql);
             PreparedStatement maxIdPStmt = con.prepareStatement(maxIdSql);
             PreparedStatement updatePStmt = con.prepareStatement(updateAutoIncrementSql)) {
            deletePStmt.setString(1, input.getFirst());
            int rowsAffected = deletePStmt.executeUpdate();

            ResultSet rs = maxIdPStmt.executeQuery();
            int maxId = 1;
            if (rs.next()) {
                maxId = rs.getInt(1) + 1;
            }
            updatePStmt.setInt(1, maxId);
            updatePStmt.executeUpdate();

            close();
            return rowsAffected > 0;
        } catch (SQLException e) {
            System.err.println("出现异常：" + e.getMessage());
            close();
            return false;
        }
    }

    /**
     * @param input 输入
     * @return 返回
     */
    public List<String> find(List<String> input) {
        List<String> result = new ArrayList<>();
        connect();
        StringBuilder sql = new StringBuilder("SELECT * FROM Flight WHERE ");
        List<Object> params = new ArrayList<>();

        try {
            if (input == null || input.isEmpty()) {
                // 空输入时查询所有航班
                sql = new StringBuilder("SELECT * FROM Flight ORDER BY fid ASC");
            } else {
                for (int i = 0; i < input.size(); i++) {
                    Pattern pattern = Pattern.compile("(<=|>=|=|<|>)");
                    Matcher matcher = pattern.matcher(input.get(i));
                    if (matcher.find()) {
                        String operator = matcher.group();
                        String[] parts = input.get(i).split(Pattern.quote(operator), 2);
                        String field = parts[0].trim().toLowerCase();
                        String value = parts[1].trim();

                        if (!Set.of("fid", "starttime", "endtime", "takeoffstation", "arrivalstation", "boardingtime", "1")
                                .contains(field)) {
                            throw new IllegalArgumentException("Invalid field: " + field);
                        }

                        sql.append(field).append(operator).append("?");
                        // 处理值类型转换
                        Object param;
                        if ("fid".equals(field) || "1".equals(field)) {
                            try {
                                param = Integer.parseInt(value);
                            } catch (NumberFormatException e) {
                                System.err.println("fid值非整数: " + value);
                                continue;
                            }
                        } else {
                            param = value; // 其他字段作为字符串处理（包括datetime）
                        }

                        // 构建SQL和参数
                        params.add(param);

                        if (i < input.size() - 1) sql.append(" AND ");
                    } else {
                        throw new IllegalArgumentException("Invalid condition: " + input.get(i));
                    }
                }

                sql.append(" ORDER BY fid ASC");
            }

            // 执行查询
            try (PreparedStatement PStmt = con.prepareStatement(sql.toString())) {
                for (int i = 0; i < params.size(); i++) {
                    Object param = params.get(i);
                    if (param instanceof Integer) {
                        PStmt.setInt(i + 1, (Integer) param);
                    } else {
                        PStmt.setString(i + 1, (String) param);
                    }
                }

                ResultSet rs = PStmt.executeQuery();

                // 结果集格式化
                while (rs.next()) {
                    String row = String.join(",",
                            rs.getString("fid"),
                            rs.getString("starttime"),
                            rs.getString("endtime"),
                            rs.getString("takeoffstation"),
                            rs.getString("arrivalstation"),
                            rs.getString("boardingtime")
                    );
                    result.add(row);
                }
            }
        } catch (SQLException | IllegalArgumentException e) {
            System.err.println("出现异常：" + e.getMessage());
        } finally {
            close();
        }
        return result;
    }

    /**
     * @param conditionColumn 条件
     * @param conditionValue 值
     * @param returnList 返回
     */
    public void find(String conditionColumn, String conditionValue, List<String> returnList) {
        connect();
        String sql = "SELECT * FROM `Flight` WHERE true";
        if (!conditionColumn.isEmpty() &&!conditionValue.isEmpty()) {
            sql += " AND `" + conditionColumn + "` =?";
        }
        try (PreparedStatement PStmt = con.prepareStatement(sql)) {
            if (!conditionColumn.isEmpty() &&!conditionValue.isEmpty()) {
                PStmt.setString(1, conditionValue);
            }
            ResultSet rs = PStmt.executeQuery();
            while (rs.next()) {
                returnList.add(rs.getString("fid"));
                returnList.add(rs.getString("starttime"));
                returnList.add(rs.getString("endtime"));
                returnList.add(rs.getString("takeoffstation"));
                returnList.add(rs.getString("arrivalstation"));
                returnList.add(rs.getString("boardingtime"));
            }
            close();
        } catch (SQLException e) {
            System.err.println("出现异常：" + e.getMessage());
            close();
        }
    }

    /**
     * @param input 输入
     * @return 返回
     */
    public boolean update(List<String> input) {
        if (input.size() != 6) { // 因为id自增不输入，所以输入需要6个参数
            return false;
        }
        connect();
        String sql = "UPDATE `Flight` SET `starttime` =?, `endtime` =?, `takeoffstation` =?, `arrivalstation` =?, `boardingtime` =? WHERE `fid` =?";
        try (PreparedStatement PStmt = con.prepareStatement(sql)) {
            PStmt.setString(1, input.get(1));
            PStmt.setString(2, input.get(2));
            PStmt.setString(3, input.get(3));
            PStmt.setString(4, input.get(4));
            PStmt.setString(5, input.get(5));
            PStmt.setString(6, input.get(0));
            int rowsAffected = PStmt.executeUpdate();
            close();
            return rowsAffected > 0;
        } catch (SQLException e) {
            System.err.println("出现异常：" + e.getMessage());
            close();
            return false;
        }
    }
}